package l1j.server.server.datatables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import l1j.server.L1DatabaseFactory;
import l1j.server.server.model.L1World;
import l1j.server.server.model.Instance.L1PcInstance;
import l1j.server.server.serverpackets.S_NPCTalkReturn;
import l1j.server.server.utils.SQLUtil;

public class CustomerServiceTable {
	
	private static Logger _log = Logger.getLogger(CustomerServiceTable.class.getName());
	
	private static CustomerServiceTable _instance;
	
	public static CustomerServiceTable getInstance() {
		if (_instance == null) {
			_instance = new CustomerServiceTable();
		}
		return _instance;
	}
	
	private int _CaseID;
	private String _Name;
	private int _GMId;
	private int _PCId;
	private String _GMName;
	private String _PCName;
	private boolean _Busy;
	private boolean _IsOnline;
	private int _Manner;
	private int _TotalCase;
	private int _TotalManner;
	private int _Rank;
	private String _queueStatus;
	
	public String getQueueStatus(){
		return _queueStatus;
	}
	
	public void setQueueStatus(String i){
		_queueStatus = i;
	}
	
	public int get_Rank(){
		return _Rank;
	}
	
	public void set_Rank(int i){
		_Rank = i;
	}
	public int get_TotalManner(){
		return _TotalManner;
	}
	
	public void set_TotalManner(int i){
		_TotalManner = i;
	}
	
	public String getName(){
		return _Name;
	}
	
	public void setName(String i){
		_Name = i;
	}
	
	public int getCaseID(){
		return _CaseID;
	}
	
	public void setCaseID(int i) {
		_CaseID = i;
	}
	
	public int getPCId(){
		return _PCId;
	}
	
	public void setPCId(int i){
		_PCId = i;
	}
	
	public String getPCName(){
		return _PCName;
	}
	
	public void setPCName(String i){
		_PCName = i;
	}
	
	public int getGMId(){
		return _GMId;
	}
	
	public void setGMId(int i){
		_GMId = i;
	}
	
	public String getGMName(){
		return _GMName;
	}
	
	public void setGMName(String i){
		_GMName = i;
	}
	
	public boolean getBusy(){
		return _Busy;
	}
	
	public void setBusy(boolean i){
		_Busy = i;
	}
	
	public boolean getIsOnline(){
		return _IsOnline;
	}
	
	public void setIsOnline(boolean i){
		_IsOnline = i;
	}
	
	public int getManner(){
		return _Manner;
	}
	
	public void setManner(int i){
		_Manner = i;
	}

	public int getTotalCase(){
		return _TotalCase;
	}
	
	public void setTotalCase(int i){
		_TotalCase = i;
	}
	
	public void storeMessage(int caseid, String sender, String receiver, String status, String message) {	
		Connection con = null;	
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("INSERT INTO cs_message SET case_id=?, sender=?, receiver=?, status=?, message=?, datetime=SYSDATE()");
			int i = 0;
			pstm.setInt(++i, caseid);
			pstm.setString(++i, sender);
			pstm.setString(++i, receiver);
			pstm.setString(++i, status);
			pstm.setString(++i, message);			
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);

		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);

		}
	}
	
	public String[] loadMessage(int caseid) {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int i = 0;
		String[] result = new String[10];
		for(i=0; i<10; i++)
			result[i] = "";
		try {
			i=1;
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT * FROM cs_message WHERE case_id=? and status = 'new' Order By datetime DESC limit 0, 9");
			pstm.setInt(1, caseid);
			rs = pstm.executeQuery();
			
			while (rs.next()) {
				result[i] = rs.getString("sender")+" : "+rs.getString("message");
				i++;
			}
		}
		catch (SQLException e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
		return result;
	}
	
	public void UpdateStatus(int caseid) {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("UPDATE cs_message SET status='end' WHERE case_id=?");
			pstm.setInt(1, caseid);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public void UpdateManner(int gmid, int thismanner) {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("UPDATE cs_gm_list SET thismanner=?, totalmanner=?, totalcase=?, average=? WHERE gmid=?");
			pstm.setInt(1, thismanner);
			loadGMList(gmid);
			int totalmanner = get_TotalManner();
			totalmanner += thismanner;
			pstm.setInt(2, totalmanner);
			int totalcase = 1;
			totalcase += getTotalCase();
			pstm.setInt(3, totalcase);
			int average = totalmanner/totalcase;
			pstm.setInt(4, average);
			pstm.setInt(5, gmid);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public String[] loadGMQueueList(int gmid) {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int i = 0;
		String[] result = new String[11];
		for(i=0; i<11; i++)
			result[i] = "";
		loadGMList(gmid);
		int average = 100;
		if(getTotalCase() != 0){
			average = get_TotalManner() / getTotalCase();
		}
		result[0] = "最後評分"+getManner()+"總處理數"+getTotalCase()+"整體" + average + "%";
		try {
			i=1;
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT rank FROM cs_queue_list WHERE queuestatus = 'new' Order By datetime ASC limit 0, 9");
			rs = pstm.executeQuery();
			while (rs.next()) {
				String Rank = "";
				switch (rs.getInt("rank")){
					case 1 :
						Rank = "一般查詢";
						break;
					case 0:
						Rank = "贊助查詢";
						break;
				}
				result[i] = "第 "+(i)+"個客戶"+"("+Rank+")";
				i++;
			}
		}
		catch (SQLException e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
		return result;
	}
	
	public void storeQueueNum(int caseid, String pcname, int rank) {	
		Connection con = null;	
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("INSERT INTO cs_queue_list SET caseid=?, name=?, rank=?, queuestatus = 'new' ,datetime=SYSDATE() ");
			pstm.setInt(1, caseid);
			pstm.setString(2, pcname);
			pstm.setInt(3, rank);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);

		}
	}
	
	public void deleteQueueNum(int i){
		Connection con = null;	
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("DELETE FROM cs_queue_list WHERE caseid=?");
			pstm.setInt(1,i);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public int loadQueueNum(int caseid) {
		int queueNo = -1;
		List<Integer> queuelist = new ArrayList<Integer>();
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		if(isCharInQueue(caseid)){
			queueNo = 11;
			try {
				con = L1DatabaseFactory.getInstance().getConnection();
				pstm = con.prepareStatement("SELECT caseid FROM cs_queue_list WHERE queuestatus = 'new' Order By datetime ASC limit 0, 9");
				rs = pstm.executeQuery();
				while (rs.next()) {
					queuelist.add(rs.getInt("caseid"));
				}
				for(int i = 0; i < queuelist.size(); i++){
					if(caseid == queuelist.get(i)){
						queueNo = i+1;
						break;
					}
				}
			}
			catch (Exception e) { 
				_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
			}
			finally {
				SQLUtil.close(rs, pstm, con);
			}
		}
		else
		{
			return queueNo;
		}
		return queueNo;
	}
	
	public void updatePcQueueList(){
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT name FROM cs_queue_list WHERE queuestatus = 'new' Order By datetime ASC limit 0, 9");
			rs = pstm.executeQuery();
			while (rs.next()) {
				L1PcInstance pc = L1World.getInstance().getPlayer(rs.getString("name"));
				int caseid = pc.getId();
				int csq = loadQueueNum(caseid);
				String htmlid = "cs_queue"+csq;				
				pc.sendPackets(new S_NPCTalkReturn(pc.getId(), htmlid));
			}
		}
		catch (Exception e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
	}
	
	public boolean isCharInQueue(int caseid) {
		boolean isInQueue = false;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT caseid FROM cs_queue_list WHERE caseid=?");
			pstm.setInt(1, caseid);
			rs = pstm.executeQuery();
			while (rs.next()) {
				isInQueue = true;
			}
		} 
		catch (Exception e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
		return isInQueue;
	}
	
	public boolean isCharInChat(int caseid) {
		boolean isInQueue = false;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT caseid FROM cs_queue_list WHERE caseid=? and queuestatus = 'chating'");
			pstm.setInt(1, caseid);
			rs = pstm.executeQuery();
			while (rs.next()) {
				isInQueue = true;
			}
		}
		catch (Exception e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
		return isInQueue;
	}
	
	public void loadQueueList() {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT caseid, name, rank, queuestatus FROM cs_queue_list WHERE queuestatus = 'new' Order By datetime ASC limit 0, 1");
			rs = pstm.executeQuery();
			while (rs.next()) {
				setCaseID(rs.getInt("caseid"));
				setName(rs.getString("name"));
				set_Rank(rs.getInt("rank"));
				setQueueStatus(rs.getString("queuestatus"));
			}
		}
		catch (Exception e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
	}
	
	public void updateQueuestatus(int caseid, String queuestatus) {
		Connection con = null;
		PreparedStatement pstm = null;
		
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("UPDATE cs_queue_list SET queuestatus=? WHERE caseid =?");
			pstm.setString(1, queuestatus);
			pstm.setInt(2, caseid);
			pstm.execute();
		}
		catch (Exception e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public void storeGMList(String  gmname, int gmid) {	
		Connection con = null;	
		PreparedStatement pstm = null;
		try {  
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("INSERT INTO cs_gm_list SET gmname=?, gmid=?, pcid=0, isonline=1, busy=0, thismanner=0, totalmanner=0, totalcase=0, average=0 ");
			pstm.setString(1, gmname);
			pstm.setInt(2, gmid);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);

		}
	}
	
	public void loadGMList(int gmid) {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT * FROM cs_gm_list WHERE gmid=?");
			pstm.setInt(1, gmid);
			rs = pstm.executeQuery();
			while (rs.next()) {
				setGMName(rs.getString("gmname"));
				setIsOnline(rs.getInt("isonline") == 0 ? false : true);
				setBusy(rs.getInt("busy") == 0 ? false : true);
				setManner(rs.getInt("thismanner"));
				set_TotalManner(rs.getInt("totalmanner"));
				setTotalCase(rs.getInt("totalcase"));
			}
		}
		catch (Exception e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
	}

	public void updateGMOnline(int gmid, boolean online) {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("UPDATE cs_gm_list SET isonline=? WHERE gmid=?");
			pstm.setBoolean(1, online);
			pstm.setInt(2, gmid);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public void updateGMBusy(int gmid, boolean busy) {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("UPDATE cs_gm_list SET busy=? WHERE gmid=?");
			pstm.setBoolean(1, busy);
			pstm.setInt(2, gmid);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public void updateGMTarget(int gmid, int pcid, String pcname) {
		Connection con = null;
		PreparedStatement pstm = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("UPDATE cs_gm_list SET pcid =? , pcname =? WHERE gmid=?");
			pstm.setInt(1, pcid);
			pstm.setString(2, pcname);
			pstm.setInt(3, gmid);
			pstm.execute();
		} catch (Exception e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public int countGMList(int gmid) {
		int result = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("SELECT count(*) as cnt FROM cs_gm_list WHERE gmid=?");
			pstm.setInt(1, gmid);
			rs = pstm.executeQuery();
			if (rs.next()) {
				result = rs.getInt("cnt");
			}
		} catch (SQLException e) {
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		} finally {
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return result;
	}
	
	public void loadGMtoPC(int gmid) {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;		
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT pcid, pcname FROM cs_gm_list Where gmid=?");
			pstm.setInt(1, gmid);
			rs = pstm.executeQuery();
			while (rs.next()) {
				setPCId(rs.getInt("pcid"));
				setPCName(rs.getString("pcname"));
			}			
		}	
		catch (Exception e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
	}
	
	public void loadPCtoGM(int pcid) {
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;		
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT gmid, gmname FROM cs_gm_list Where pcid=?");
			pstm.setInt(1, pcid);
			rs = pstm.executeQuery();
			while (rs.next()) {
				setGMId(rs.getInt("gmid"));
				setGMName(rs.getString("gmname"));
			}			
		}	
		catch (Exception e) { 
			_log.log(Level.SEVERE, e.getLocalizedMessage(), e);
		}
		finally {
			SQLUtil.close(rs, pstm, con);
		}
	}
}